package com.myclover.db.service;

import java.io.*;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.myclover.db.bean.Person;
import com.myclover.db.bean.States;

import android.R.integer;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;

public class DBService
{
	@SuppressWarnings("unused")
	private Context context;
	private DBOpenHelper dbHelper;

	public DBService(Context context)
	{
		dbHelper = new DBOpenHelper(context);
	}

	
	public void save(Person p)
	{
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL(
				"insert into t_person (firstname , lastname , sex ,dob , cellphone , homephone ,email ,regdate ,addr1 ,addr2 ,city , state , zip ,memo, f01 ,f02) values (? , ? , ? , ?, ? , ? , ? ,  strftime('%m-%d-%Y','now','localtime'),? , ? , ? , ?,? , ? , ? , ? )",
				new Object[]
				{ p.getFirstname(), p.getLastname(), p.getSex(), p.getDob(),p.getCellphone(),p.getHomephone(), p.getEmail(),  p.getAddr1(), p.getAddr2(), p.getCity(), p.getState(), p.getZip(), p.getMemo(), p.getF01(), p.getF02() });
	}

	public void update(Person p)
	{
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL(
				"update t_person set firstname = ?, lastname = ?, sex = ?,dob = ?, cellphone = ?, homephone = ?,email = ?,addr1 = ?,addr2 = ?,city = ?, state = ?, zip = ?,memo = ?, f01 = ?,f02 = ? where id = ?  ",
				new Object[]
				{ p.getFirstname(), p.getLastname(), p.getSex(), p.getDob(),p.getCellphone(),p.getHomephone(), p.getEmail(), p.getAddr1(), p.getAddr2(), p.getCity(), p.getState(), p.getZip(), p.getMemo(), p.getF01(), p.getF02(), p.getId() });
	}
	
	public void delete(Integer... ids)
	{
		if (ids.length > 0)
		{
			StringBuffer sb = new StringBuffer();
			for (@SuppressWarnings("unused")
			int id : ids)
			{
				sb.append("?").append(",");
			}
			sb.deleteCharAt(sb.length() - 1);
			SQLiteDatabase db = dbHelper.getWritableDatabase();
			db.execSQL("delete from t_person where id in (" + sb + ")",
					(Object[]) ids);
		}
	}

	//----begin----search func-----
	public Person findById(Integer id)
	{
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cs = db.rawQuery("select * from t_person where id = ? ",
				new String[]
				{ String.valueOf(id) });
		if (cs.moveToNext())
		{
			Person p = new Person();
			p.setId(cs.getInt(cs.getColumnIndex("id")));
			p.setFirstname(cs.getString(cs.getColumnIndex("firstname")));
			p.setLastname(cs.getString(cs.getColumnIndex("lastname")));
			p.setSex(cs.getString(cs.getColumnIndex("sex")));
			p.setDob(cs.getString(cs.getColumnIndex("dob")));
			p.setCellphone(cs.getString(cs.getColumnIndex("cellphone")));
			p.setHomephone(cs.getString(cs.getColumnIndex("homephone")));
			p.setEmail(cs.getString(cs.getColumnIndex("email")));
			p.setRegdate(cs.getString(cs.getColumnIndex("regdate")));
			p.setAddr1(cs.getString(cs.getColumnIndex("addr1")));
			p.setAddr2(cs.getString(cs.getColumnIndex("addr2")));
			p.setCity(cs.getString(cs.getColumnIndex("city")));
			p.setState(cs.getString(cs.getColumnIndex("state")));
			p.setZip(cs.getString(cs.getColumnIndex("zip")));
			p.setMemo(cs.getString(cs.getColumnIndex("memo")));
			p.setF01(cs.getString(cs.getColumnIndex("f01")));
			p.setF02(cs.getString(cs.getColumnIndex("f02")));
			
			cs.close();
			return p;
		}

		return null;
	}

	public List<Person> findByName1(String name, int startNum, int perNum )
	{
		
		String str = "'%"+name+"%'";
		List<Person> lists = new ArrayList<Person>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cs = db.rawQuery("select * from t_person where firstname like " + str + " limit " + startNum+"," +perNum, null);
				//new String[]{ name });
				
		while (cs.moveToNext())
		{
			Person p = new Person();
			p.setId(cs.getInt(cs.getColumnIndex("id")));
			p.setFirstname(cs.getString(cs.getColumnIndex("firstname")));
			p.setLastname(cs.getString(cs.getColumnIndex("lastname")));
			p.setSex(cs.getString(cs.getColumnIndex("sex")));
			p.setDob(cs.getString(cs.getColumnIndex("dob")));
			p.setCellphone(cs.getString(cs.getColumnIndex("cellphone")));
			p.setHomephone(cs.getString(cs.getColumnIndex("homephone")));
			p.setEmail(cs.getString(cs.getColumnIndex("email")));
			p.setRegdate(cs.getString(cs.getColumnIndex("regdate")));
			p.setAddr1(cs.getString(cs.getColumnIndex("addr1")));
			p.setAddr2(cs.getString(cs.getColumnIndex("addr2")));
			p.setCity(cs.getString(cs.getColumnIndex("city")));
			p.setState(cs.getString(cs.getColumnIndex("state")));
			p.setZip(cs.getString(cs.getColumnIndex("zip")));
			p.setMemo(cs.getString(cs.getColumnIndex("memo")));
			p.setF01(cs.getString(cs.getColumnIndex("f01")));
			p.setF02(cs.getString(cs.getColumnIndex("f02")));
			lists.add(p);
		}
		cs.close();
		return lists;
	}
	
	public List<Person> findByName2(String name, int startNum, int perNum)
	{
		String str = "'%"+name+"%'";
		List<Person> lists = new ArrayList<Person>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cs = db.rawQuery("select * from t_person where lastname like " + str+ " limit " + startNum+ "," +perNum, null);
				//new String[]{ name });
				
		while (cs.moveToNext())
		{
			Person p = new Person();
			p.setId(cs.getInt(cs.getColumnIndex("id")));
			p.setFirstname(cs.getString(cs.getColumnIndex("firstname")));
			p.setLastname(cs.getString(cs.getColumnIndex("lastname")));
			p.setSex(cs.getString(cs.getColumnIndex("sex")));
			p.setDob(cs.getString(cs.getColumnIndex("dob")));
			p.setCellphone(cs.getString(cs.getColumnIndex("cellphone")));
			p.setHomephone(cs.getString(cs.getColumnIndex("homephone")));
			p.setEmail(cs.getString(cs.getColumnIndex("email")));
			p.setRegdate(cs.getString(cs.getColumnIndex("regdate")));
			p.setAddr1(cs.getString(cs.getColumnIndex("addr1")));
			p.setAddr2(cs.getString(cs.getColumnIndex("addr2")));
			p.setCity(cs.getString(cs.getColumnIndex("city")));
			p.setState(cs.getString(cs.getColumnIndex("state")));
			p.setZip(cs.getString(cs.getColumnIndex("zip")));
			p.setMemo(cs.getString(cs.getColumnIndex("memo")));
			p.setF01(cs.getString(cs.getColumnIndex("f01")));
			p.setF02(cs.getString(cs.getColumnIndex("f02")));
			lists.add(p);
		}
		cs.close();
		return lists;
	}
	
	
	public List<Person> findAll()
	{
		List<Person> lists = new ArrayList<Person>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cs = db.rawQuery("select * from t_person", null);
		while (cs.moveToNext())
		{
			Person p = new Person();
			p.setId(cs.getInt(cs.getColumnIndex("id")));
			p.setFirstname(cs.getString(cs.getColumnIndex("firstname")));
			p.setLastname(cs.getString(cs.getColumnIndex("lastname")));
			p.setSex(cs.getString(cs.getColumnIndex("sex")));
			p.setDob(cs.getString(cs.getColumnIndex("dob")));
			p.setCellphone(cs.getString(cs.getColumnIndex("cellphone")));
			p.setHomephone(cs.getString(cs.getColumnIndex("homephone")));
			p.setEmail(cs.getString(cs.getColumnIndex("email")));
			p.setRegdate(cs.getString(cs.getColumnIndex("regdate")));
			p.setAddr1(cs.getString(cs.getColumnIndex("addr1")));
			p.setAddr2(cs.getString(cs.getColumnIndex("addr2")));
			p.setCity(cs.getString(cs.getColumnIndex("city")));
			p.setState(cs.getString(cs.getColumnIndex("state")));
			p.setZip(cs.getString(cs.getColumnIndex("zip")));
			p.setMemo(cs.getString(cs.getColumnIndex("memo")));
			p.setF01(cs.getString(cs.getColumnIndex("f01")));
			p.setF02(cs.getString(cs.getColumnIndex("f02")));
			lists.add(p);
		}
		cs.close();
		return lists;
	}
	//----end----search func-----

	

	public List<Person> getPerosns(int startNum, int perNum)
	{
		List<Person> lists = new ArrayList<Person>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cs = db.rawQuery("select * from t_person limit ? , ? ",
				new String[]
				{ String.valueOf(startNum), String.valueOf(perNum) });
		while (cs.moveToNext())
		{
			Person p = new Person();
			p.setId(cs.getInt(cs.getColumnIndex("id")));
			p.setFirstname(cs.getString(cs.getColumnIndex("firstname")));
			p.setLastname(cs.getString(cs.getColumnIndex("lastname")));
			p.setSex(cs.getString(cs.getColumnIndex("sex")));
			p.setDob(cs.getString(cs.getColumnIndex("dob")));
			p.setCellphone(cs.getString(cs.getColumnIndex("cellphone")));
			p.setHomephone(cs.getString(cs.getColumnIndex("homephone")));
			p.setEmail(cs.getString(cs.getColumnIndex("email")));
			p.setRegdate(cs.getString(cs.getColumnIndex("regdate")));
			p.setAddr1(cs.getString(cs.getColumnIndex("addr1")));
			p.setAddr2(cs.getString(cs.getColumnIndex("addr2")));
			p.setCity(cs.getString(cs.getColumnIndex("city")));
			p.setState(cs.getString(cs.getColumnIndex("state")));
			p.setZip(cs.getString(cs.getColumnIndex("zip")));
			p.setMemo(cs.getString(cs.getColumnIndex("memo")));
			p.setF01(cs.getString(cs.getColumnIndex("f01")));
			p.setF02(cs.getString(cs.getColumnIndex("f02")));
			lists.add(p);
		}
		cs.close();
		return lists;
	}

	public long getTotalCounts()
	{
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cs = db.rawQuery("select count(*) from t_person ", null);
		if (cs.moveToNext())
		{
			return cs.getLong(0);
		}
		return 0;
	}
	
	public long getTotalCountsById(Integer id)
	{
		SQLiteDatabase db= dbHelper.getReadableDatabase();
		Cursor cs = db.rawQuery("select count(*) from t_person where id = ? ",
				new String[]{ String.valueOf(id) });
		if(cs.moveToNext())
			return cs.getLong(0);
		return 0;
	}
	
	public long getTotalCountsByName1(String name)
	{
		String str = "'%"+name+"%'";
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cs = db.rawQuery("select count(*) from t_person where firstname like " + str , null);
		if(cs.moveToNext())
			return cs.getLong(0);
		return 0;
	}
	
	public long getTotalCountsByName2(String name)
	{
		String str = "'%"+name+"%'";
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cs = db.rawQuery("select count(*) from t_person where lastname like " + str , null);
		if(cs.moveToNext())
			return cs.getLong(0);
		return 0;
	}
	
	public void exportCSV()
	{
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor c = db.rawQuery("select id, firstname, lastname, sex, dob, cellphone, homephone, email, regdate, addr1, addr2, city, state, zip, memo from t_person", null);
		
		int rowCount = 0;
		int colCount = 0;
		String fileName = "Customer.csv";
		FileWriter fw;
		BufferedWriter bfw;
		File sdCardDir =  Environment.getExternalStorageDirectory();
		File SDCARD_PATH = new File(sdCardDir + "/MoleQ/");
		if (!SDCARD_PATH.exists())
			SDCARD_PATH.mkdirs();
		File saveFile = new File(SDCARD_PATH, fileName);
		try
		{
			rowCount = c.getCount();
			colCount = c.getColumnCount();
			fw = new FileWriter(saveFile);
			bfw = new BufferedWriter(fw);
			if(rowCount>0)
			{
				c.moveToNext();
				for (int i = 0; i < colCount; i++)
				{
					if(i != colCount -1)
						bfw.write(c.getColumnName(i) + ',');
					else 
						bfw.write(c.getColumnName(i));
				}
				bfw.newLine();
				
				for (int i = 0; i < rowCount; i++)
				{
					c.moveToPosition(i);
					for (int j = 0; j < colCount; j++)
					{
						if(j != colCount -1)
							bfw.write(c.getString(j) + ',');
						else 
							bfw.write(c.getString(j));
					}
					bfw.newLine();
				}
			}
			bfw.flush();
			bfw.close();
		} catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			c.close();
		}
	}
	
	public List<States> getStatesById(String Id)
	{
		String str = "'%" + Id + "%'";
		List<States> list = new ArrayList<States>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cs = db.rawQuery("select * from t_states where state_id like " + str, null);
		if (cs == null)
			return null;
		while (cs.moveToNext())
		{
			States item = new States();
			item.setState_id(cs.getString(cs.getColumnIndex("state_id")));
			item.setState_name(cs.getString(cs.getColumnIndex("state_name")));
			list.add(item);
		}
		cs.close();
		return list;
	}
	
	public List<States> getStates()
	{
		List<States> list = new ArrayList<States>();
		String sql = "select * from t_states";
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return null;
		while (cursor.moveToNext())
		{
			States item = new States();
			item.setState_id(cursor.getString(0));
			item.setState_name(cursor.getString(1));
			list.add(item);
		}
		cursor.close();
		return list;
	}

	public void closeDB()
	{
		dbHelper.close();
	}
}
